1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Public Class frmBusInfo
5
6 Sub Reset()
7 txtDriverName.Text = ""
8 txtSupporterName.Text = ""
9 txtContactNo.Text = ""
10 txtContactNo_S.Text = ""
11 txtBus.Text = ""
12 txtBusNo.Text = ""
13 txtBusNo.Focus()
14 btnSave.Enabled = True
15 btnUpdate.Enabled = False
16 btnDelete.Enabled = False
17 End Sub
18 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
19 Me.Close()
20 End Sub
21
22 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
23 If txtBusNo.Text = "" Then
24 MessageBox.Show("Please enter bus no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
25 txtBusNo.Focus()
26 Return
27 End If
28 If txtDriverName.Text = "" Then
29 MessageBox.Show("Please enter driver name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
30 txtDriverName.Focus()
31 Return
32 End If
33 If txtContactNo.Text = "" Then
34 MessageBox.Show("Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
35 txtContactNo.Focus()
36 Return
37 End If
38 Try
39 con = New SqlConnection(cs)
40 con.Open()
41 Dim ct As String = "select BusNo from BusInfo where BusNo=@d1"
42 cmd = New SqlCommand(ct)
43 cmd.Parameters.AddWithValue("@d1", txtBusNo.Text)
44 cmd.Connection = con
45 rdr = cmd.ExecuteReader()
46
47 If rdr.Read() Then
48 MessageBox.Show("Bus no. Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
49 txtBusNo.Text = ""
50 txtBusNo.Focus()
51 If (rdr IsNot Nothing) Then
52 rdr.Close()
53 End If
54 Return
55 End If
56 con = New SqlConnection(cs)
57 con.Open()
58 Dim cb As String = "insert into BusInfo(BusNo,DriverName,ContactNo,SupporterName,S_ContactNo) VALUES (@d1,@d2,@d3,@d4,@d5)"
59 cmd = New SqlCommand(cb)
60 cmd.Connection = con
61 cmd.Parameters.AddWithValue("@d1", txtBusNo.Text)
62 cmd.Parameters.AddWithValue("@d2", txtDriverName.Text)
63 cmd.Parameters.AddWithValue("@d3", txtContactNo.Text)
64 cmd.Parameters.AddWithValue("@d4", txtSupporterName.Text)
65 cmd.Parameters.AddWithValue("@d5", txtContactNo_S.Text)
66 cmd.ExecuteNonQuery()
67 con.Close()
68 Dim st As String = "added the new bus having bus no. '" & txtBusNo.Text & "'"
69 LogFunc(lblUser.Text, st)
70 MessageBox.Show("Successfully saved", "Bus Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
71 btnSave.Enabled = False
72 Getdata()
73 Catch ex As Exception
74 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
75 End Try
76 End Sub
77
78
79 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
80 If txtBusNo.Text = "" Then
81 MessageBox.Show("Please enter bus no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
82 txtBusNo.Focus()
83 Return
84 End If
85 If txtDriverName.Text = "" Then
86 MessageBox.Show("Please enter driver name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
87 txtDriverName.Focus()
88 Return
89 End If
90 If txtContactNo.Text = "" Then
91 MessageBox.Show("Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
92 txtContactNo.Focus()
93 Return
94 End If
95 Try
96 con = New SqlConnection(cs)
97 con.Open()
98 Dim cb As String = "Update BusInfo set BusNo=@d2, DriverName=@d3, ContactNo=@d4, SupporterName=@d5, S_ContactNo=@d6 where BusNo=@d1"
99 cmd = New SqlCommand(cb)
100 cmd.Connection = con
101 cmd.Parameters.AddWithValue("@d1", txtBus.Text)
102 cmd.Parameters.AddWithValue("@d2", txtBusNo.Text)
103 cmd.Parameters.AddWithValue("@d3", txtDriverName.Text)
104 cmd.Parameters.AddWithValue("@d4", txtContactNo.Text)
105 cmd.Parameters.AddWithValue("@d5", txtSupporterName.Text)
106 cmd.Parameters.AddWithValue("@d6", txtContactNo_S.Text)
107 cmd.ExecuteNonQuery()
108 con.Close()
109 Dim st As String = "updated the bus info having bus no. '" & txtBusNo.Text & "'"
110 LogFunc(lblUser.Text, st)
111 MessageBox.Show("Successfully updated", "Bus Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
112 btnUpdate.Enabled = False
113 Getdata()
114 Catch ex As Exception
115 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
116 End Try
117 End Sub
118 Public Sub Getdata()
119 Try
120 con = New SqlConnection(cs)
121 con.Open()
122 cmd = New SqlCommand("SELECT RTRIM(BusNo), RTRIM(DriverName), RTRIM(ContactNo), RTRIM(SupporterName), RTRIM(S_ContactNo) from BusInfo order by BusNo", con)
123 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
124 dgw.Rows.Clear()
125 While (rdr.Read() = True)
126 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4))
127 End While
128 con.Close()
129 Catch ex As Exception
130 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
131 End Try
132 End Sub
133 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
134 Reset()
135 End Sub
136
137
138 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
139 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
140 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
141 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
142 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
143 End If
144 Dim b As Brush = SystemBrushes.ControlText
145 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
146
147 End Sub
148
149 Private Sub frmRegistration_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
150 Getdata()
151 End Sub
152
153 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
154 Try
155 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
156 txtBus.Text = dr.Cells(0).Value.ToString()
157 txtBusNo.Text = dr.Cells(0).Value.ToString()
158 txtDriverName.Text = dr.Cells(1).Value.ToString()
159 txtContactNo.Text = dr.Cells(2).Value.ToString()
160 txtSupporterName.Text = dr.Cells(3).Value.ToString()
161 txtContactNo_S.Text = dr.Cells(4).Value.ToString()
162 btnUpdate.Enabled = True
163 btnSave.Enabled = False
164 btnDelete.Enabled = True
165 Catch ex As Exception
166 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
167 End Try
168 End Sub
169
170 Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
171 Try
172 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
173 DeleteRecord()
174 End If
175 Catch ex As Exception
176 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
177 End Try
178 End Sub
179 Private Sub DeleteRecord()
180
181 Try
182 Dim RowsAffected As Integer = 0
183 con = New SqlConnection(cs)
184 con.Open()
185 Dim cl As String = "select BusInfo.BusNo from BusInfo,BusCardHolder_Student where BusInfo.BusNo=BusCardHolder_Student.BusNo and BusInfo.BusNo=@d1"
186 cmd = New SqlCommand(cl)
187 cmd.Connection = con
188 cmd.Parameters.AddWithValue("@d1", txtBus.Text)
189 rdr = cmd.ExecuteReader()
190 If rdr.Read Then
191 MessageBox.Show("Unable to delete..Already in use in Bus Holder [Student]", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
192 If Not rdr Is Nothing Then
193 rdr.Close()
194 End If
195 Exit Sub
196 End If
197 con = New SqlConnection(cs)
198 con.Open()
199 Dim cl1 As String = "select BusInfo.BusNo from BusInfo,BusCardHolder_Staff where BusInfo.BusNo=BusCardHolder_Staff.BusNo and BusInfo.BusNo=@d1"
200 cmd = New SqlCommand(cl1)
201 cmd.Connection = con
202 cmd.Parameters.AddWithValue("@d1", txtBus.Text)
203 rdr = cmd.ExecuteReader()
204 If rdr.Read Then
205 MessageBox.Show("Unable to delete..Already in use in Bus Holder [Staff]", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
206 If Not rdr Is Nothing Then
207 rdr.Close()
208 End If
209 Exit Sub
210 End If
211 con = New SqlConnection(cs)
212 con.Open()
213 Dim cq As String = "delete from BusInfo where BusNo=@d1"
214 cmd = New SqlCommand(cq)
215 cmd.Parameters.AddWithValue("@d1", txtBus.Text)
216 cmd.Connection = con
217 RowsAffected = cmd.ExecuteNonQuery()
218 If RowsAffected > 0 Then
219 Dim st As String = "deleted the bus having bus no. '" & txtBusNo.Text & "'"
220 LogFunc(lblUser.Text, st)
221 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
222 Getdata()
223 Reset()
224 Else
225 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
226 Reset()
227 End If
228 If con.State = ConnectionState.Open Then
229 con.Close()
230
231 End If
232 Catch ex As Exception
233 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
234 End Try
235 End Sub
236
237 End Class
